package com.qishon.apiTest;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description    封装excel操作
 * @Author         sangdi.he
 * @Time           2016-08-03 11:27
 */
public class Excel {


    //TODO Excel 如何做到不频繁的打开,关闭文件。

    private Workbook workbook;
    private boolean fileExist = true;
    private String excelFilePath;   //存储传入的路径,在写excel时用到

    /**
     * @Description    构造器,传入文件路径,初始化内部的Workbook对象
     * @param pathName 文件路径
     * @Author         sangdi.he
     * @Time           2016-08-03 12:41
     * @Update_History 无
     */
    public Excel(String pathName) {
        try {
            FileInputStream fin = new FileInputStream(pathName);
            this.workbook = new XSSFWorkbook(fin);
            excelFilePath = pathName;
        }
        catch (IOException e) {
            fileExist = false;
            excelFilePath = pathName;
        }
    }

    /**
     * @Description    workbook的getter。方便想要读取某一行,某一个cell时可以用
     * @return         返回Workbook对象
     * @throws         IOException 如果IO出错抛出此异常
     * @Author         sangdi.he
     * @Time           2016-08-03 13:29
     * @Update_History 无
     */
    public Workbook getWorkbook() throws IOException{
        //如果文件存在
        if (fileExist) {
            return this.workbook;
        }
        //如果文件不存在
        else {
            throw new IOException(excelFilePath + " :this file may not exist");
        }
    }

    /**
     * @Description    关闭workbook
     * @Author         sangdi.he
     * @Time           2016-08-03 13:31
     * @Update_History 无
     */
    void closeWorkbook() {
        try {
            this.workbook.close();
        }
        catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @Description     读取数据Excel文件中的某一个sheet,获取该sheet所包含的用例
     * @param filePath  文件路径
     * @param sheetName sheet名
     * @return          返回一个列表,包含用例(Api)对象
     * @throws          IOException 如果文件IO出错,抛出异常
     * @Author          sangdi.he
     * @Time            2016-08-03 13:32
     * @Update_Time     2016-08-12 09:28
     * @Update_History  加入对Excel可能存在的空行,空列的处理,数据取出更可靠
     */
    public static List<Api> getApis(String filePath, String sheetName) throws IOException {
        Excel excelRead = new Excel(filePath);
        List<Api> apis = new ArrayList<>();
        Map<Integer, String> argName = new HashMap<>(); //存储参数名
        int lastRowNum = excelRead.getWorkbook().getSheet(sheetName).getLastRowNum();   //获取该sheet最后一行行号

        //获取参数名
        //如果第一行从某一列开始,格子内容为空了,就跳出循环
        //这样可以保证不会取到空参数
        for (Cell c : excelRead.getWorkbook().getSheet(sheetName).getRow(0)) {
            if (c.toString().equals("")) {
                break;
            }
            argName.put(c.getColumnIndex(), c.toString());
        }

        Integer indexOfTestCaseName = -1;
        // 获取TestCaseName的列号
        for (Integer key: argName.keySet()) {
            if (argName.get(key).equals("TestCaseName")) {
                indexOfTestCaseName = key;
                break;
            }
        }

        if (indexOfTestCaseName == -1) {
            throw new IOException("Can's find Row<TestCaseName>, please fix it in sheet: " + sheetName);
        }
        //判断最后一行的TestCaseName是否为空,如果为空说明此行没有取的必要,把lastRowNum减1
        Sheet currentSheet = excelRead.getWorkbook().getSheet(sheetName);
        while (currentSheet.getRow(lastRowNum).getCell(indexOfTestCaseName).toString().equals("")) {
            lastRowNum--;
        }

        //获取sheet中所有的用例数据,每一行保存到一个FBIPadApi对象中,添加到apis中返回出去
        for (int i=1; i<=lastRowNum; i++) {
            Row current_row = excelRead.getWorkbook().getSheet(sheetName).getRow(i);
            Api api = new Api();
            //获取一行用例的参数值,根据对应的参数名,组成Map。存入FBIPadApi对象中,最后添加到apis中返回出去
            //for (Cell cell: current_row) { //for each循环会跳过null的cell导致少参数，改用传统的for循环
            for (int colum=0; colum<current_row.getLastCellNum(); colum++) {
                //如果Cell为空
                if (current_row.getCell(colum) == null) {
                    api.addParam(argName.get(colum), "");
                    continue;
                }
                //cell不为空直接加入参数
                api.addParam(argName.get(colum), current_row.getCell(colum).toString());
            }
            apis.add(api);
        }

        excelRead.closeWorkbook();
        return apis;
    }

    /**
     * @Description     写excel统一的方法调用入口
     * @param sheetName sheet名
     * @param rowNum    行号
     * @param colNum    列号
     * @param write     写入的字符串
     * @return          写入成功返回true,失败返回false
     * @Author          sangdi.he
     * @Time            2016-08-03 13:33
     * @Update_History  无
     */
    public boolean write(String sheetName, int rowNum, int colNum, String write) {
        boolean flag = false;

        //如果文件存在
        if (fileExist) {
            if (this.writeExsist(excelFilePath, sheetName, rowNum, colNum, write)) {
                flag = true;
            }
        }
        //如果文件不存在
        else {
            if (this.writeNew(excelFilePath, sheetName, rowNum, colNum, write)) {
                flag = true;
                fileExist = true;
            }
        }

        return flag;
    }

    /**
     * @Description    写新文件
     * @param path     文件路径
     * @param sheet    sheet名
     * @param rowNum   行号从0开始
     * @param colNum   列号从0开始
     * @param write    需要写入的字符串(java.lang.String)
     * @return         boolean: 1写入成功,0写入失败
     * @Author         sangdi.he
     * @Time           2016-08-03 13:35
     * @Update_History 无
     */
    private boolean writeNew(String path, String sheet, int rowNum, int colNum, String write) {
        boolean flag = false;
        try {
            Workbook wb = new XSSFWorkbook();
            Cell cell = wb.createSheet(sheet).createRow(rowNum).createCell(colNum);
            cell.setCellValue(write);
            if (cell.getStringCellValue().equals(write)) {
                flag = true;
            }
            FileOutputStream fileOut = new FileOutputStream(path);
            wb.write(fileOut);
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * @Description    写已存在的文件
     * @param path     文件路径名
     * @param sheet    sheet名
     * @param rowNum   行号从0开始
     * @param colNum   列号从0开始
     * @param write    要写入的字符串
     * @return         写入是否成功,{@code true}代表写入成功,{@code false}代表失败
     * @Author         sangdi.he
     * @Time           2016-08-03 13:35
     * @Update_History 无
     */
    private boolean writeExsist(String path, String sheet, int rowNum, int colNum, String write) {
        boolean flag = false;
        //存在的文件中，sheet/row/cell都有可能为空，所以还得继续检查
        try {
//			Sheet st;
//			Row r;
//			Cell c;
            Cell cell = null;
            Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            int bzw = 0;
            //判断excel状况。sheet row cell均有可能不存在
            if (workbook.getSheet(sheet) == null) {
                //sheet不存在
                bzw = 1;
            }
            else if (workbook.getSheet(sheet).getRow(rowNum) == null) {
                //row不存在
                bzw = 2;
            }
            else if (workbook.getSheet(sheet).getRow(rowNum).getCell(colNum) == null) {
                //cell不存在
                bzw = 3;
            }
            else {
                //sheet row cell 都存在
                bzw = 4;
            }
            //对应不同的状态，进行不同的操作
            switch (bzw) {
                case 1://sheet 不存在
                    workbook.createSheet(sheet).createRow(rowNum).createCell(colNum).setCellValue(write);
                    break;
                case 2://row 不存在
                    workbook.getSheet(sheet).createRow(rowNum).createCell(colNum).setCellValue(write);
                    break;
                case 3://cell 不存在
                    workbook.getSheet(sheet).getRow(rowNum).createCell(colNum).setCellValue(write);
                    break;
                case 4://sheet row cell 都存在
                    workbook.getSheet(sheet).getRow(rowNum).getCell(colNum).setCellValue(write);
                    break;

                default:
                    break;
            }
            cell = workbook.getSheet(sheet).getRow(rowNum).getCell(colNum);
            if (cell.getStringCellValue().equals(write)) {
                flag = true;
            }
            FileOutputStream fileOut = new FileOutputStream(path);
            workbook.write(fileOut);
            workbook.close();
        }
        catch (IOException e) {
            e.printStackTrace();
        }
        return flag;
    }
}
